package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.sl.au.entity.ConfigDetailForCRM;
import com.sl.au.entity.ConfigMainForCRM;
import com.sl.au.entity.MFOldPartRelation;
import com.sl.au.entity.ProductPlanForCRM;

public interface ConfigDetailForCRMRepository extends JpaRepository<ConfigDetailForCRM,String> {

	
	@Query("select c from ConfigMainForCRM c where opProductPlanId=?1 ")
	List<ConfigMainForCRM> getconfigmainlist(Integer opId);
	
	@Query("select c from ConfigDetailForCRM c where (c.cPartPicNumDes like '%'+?1+'%' "
			+ " or c.cPartName like '%'+?1+'%' "
			+ "or c.cMaterial like '%'+?1+'%' or c.cConfigurationType like '%'+?1+'%') and  c.opProductPlanId=?2 and c.cConfigurationType=?3 order by c.cConfigurationType,c.cOrder ")
	List<ConfigDetailForCRM> getDetails(String key,Integer opId,String ntype, Pageable pageable);
	
	
	@Query("select c from ConfigDetailForCRM c where opProductPlanId=?1 ")
	List<ConfigDetailForCRM> getDetails(Integer opId, Pageable pageable);
	
	@Query("select count(*) from ConfigDetailForCRM c where (c.cPartPicNumDes like '%'+?1+'%' "
			+ " or c.cPartName like '%'+?1+'%' "
			+ "or c.cMaterial like '%'+?1+'%' or c.cConfigurationType like '%'+?1+'%') and  c.opProductPlanId=?2 and c.cConfigurationType=?3 ")
	long getDetailsCount(String key,Integer opId,String ntype);
	
	@Query("select count(*) from ConfigDetailForCRM c where opProductPlanId=?1 ")
	long getDetailsCount(Integer opId);
	
	
	@Query("select c from ConfigDetailForCRM c where c.id = ?1")
	ConfigDetailForCRM findOne(Integer id);
	
	
	@Query("select c from ProductPlanForCRM c where c.cOrderUnitName like '%'+?1+'%' or c.cContractNumber like '%'+?1+'%' "
			+ " or c.cProductPlanNumber like '%'+?1+'%' "
			+ "or c.cProductType like '%'+?1+'%' ")
	List<ProductPlanForCRM> getContractDetails(String key, Pageable pageable);
	
	@Query("select count(*) from ProductPlanForCRM c where c.cOrderUnitName like '%'+?1+'%' or c.cContractNumber like '%'+?1+'%' "
			+ " or c.cProductPlanNumber like '%'+?1+'%' "
			+ "or c.cProductType like '%'+?1+'%' ")
	long getDetailsContractCount(String key);
	
	@Query("select c from MFOldPartRelation c where c.nUpPartId=0 and c.nPartId is not null and (c.cPartPicNum like '%'+?1+'%' "
			+ " or c.cPartName like '%'+?1+'%' "
			+ "or c.cMaterial like '%'+?1+'%') ")
	List<MFOldPartRelation> getMFDetails(String key, Pageable pageable);
	
	@Query("select count(*) from MFOldPartRelation c where c.cUpPartPicNum like '%'+?1+'%' or c.cPartPicNum like '%'+?1+'%' "
			+ " or c.cPartName like '%'+?1+'%' "
			+ "or c.cMaterial like '%'+?1+'%'  ")
	long getDetailsMFCount(String key);
}
